using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void DirectoryDeleteContents(SqlString path, SqlInt16 daysToKeep, SqlString fileExtension)
    {
        Int16 errSeverity = 16;

        try
        {
            Int16 iDaysToKeep = Convert.ToInt16(daysToKeep.ToString());

            DateTime dateToday = DateTime.Today.AddDays(-iDaysToKeep);
            DirectoryInfo di = new DirectoryInfo(path.ToString());
            Array arrFiles = di.GetFiles();

            SqlMetaData[] filesDeleted = new SqlMetaData[2];
            filesDeleted[0] = new SqlMetaData("Name", SqlDbType.NVarChar, 250);
            filesDeleted[1] = new SqlMetaData("Extension", SqlDbType.NVarChar, 250);

            // construct the record which holds metadata and data buffers
            SqlDataRecord record = new SqlDataRecord(filesDeleted);

            // cache a SqlPipe instance to avoid repeated calls to  
            // SqlContext.GetPipe()
            SqlPipe sqlpipe = SqlContext.Pipe;

            // send the metadata, do not send the values in the data record
            sqlpipe.SendResultsStart(record);

            foreach (FileInfo fi in arrFiles)
            {
                if (fi.Extension == fileExtension && fi.LastWriteTime < dateToday)
                {
                    record.SetSqlString(0, fi.Name);
                    record.SetSqlString(1, fi.Extension);
                    fi.Delete();

                    sqlpipe.SendResultsRow(record);
                }
            }

            // signal end of results
            sqlpipe.SendResultsEnd();
        }
        catch (Exception ex)
        {
            // Connect to server in event of error
            SqlConnection conn = new SqlConnection("context connection=true");
            conn.Open();

            // Generate error response to SQL Server
            string msg = string.Format("RAISERROR('{0}',{1},1)", ex.Message.Replace('\'', ' '), errSeverity);
            SqlCommand cmd = new SqlCommand(msg, conn);

            try
            {
                // Execute error on SQL Server
                SqlPipe sqlpipe = SqlContext.Pipe;
                sqlpipe.ExecuteAndSend(cmd);
                Exception newEx = new Exception(msg);
                throw newEx;
            }
            catch { } // Prevent error duplication
        }
    }
};
